﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using RCREEE.Library.Entities;

namespace RCREEE.Library
{
    public static partial class DataManager
    {

        #region public Methods

        #region Select Methods

        public static DataTable GetAllMasterContacts(int entityId, int languageCode, int countryId)
        {

            #region Setting Command Text

            //            _Command.CommandText = @"SELECT     ContactMaster.ContactId, ContactDetails.Name AS ContactName, ContactMaster.CountryId, CountryDetails.CountryName, ContactMaster.EntityId, 
            //                                                          EntityItems.Name AS EntityName, ContactMaster.IsRE, ContactMaster.IsEE, ContactMaster.ParentContactId, ContactMaster.QualificationLevelId, 
            //                                                          QualificationLevelItems.Name AS QualificationLevelName, ContactMaster.QualificationFieldId, QualificationFieldItems.Name AS QualificationFieldName, 
            //                                                          ContactMaster.QualificationSubFieldId, QualificationSubFieldItems.Name AS QualificationSubFieldName, ContactMaster.FieldId, FieldItems.Name AS FieldName, 
            //                                                          ContactDetails.LanguageCode, ContactDetails.PhoneNumber, ContactDetails.MobileNumber, ContactDetails.FaxNumber, ContactDetails.Email, 
            //                                                          ContactDetails.Website, ContactDetails.Description, ContactDetails.Experience, ContactDetails.NumberOfExperts, ContactDetails.Address, 
            //                                                          ContactDetails.JobDescription
            //                                    FROM         ContactMaster INNER JOIN
            //                                                          ContactDetails ON ContactMaster.ContactId = ContactDetails.ContactId AND ContactDetails.LanguageCode = @LanguageCode INNER JOIN
            //                                                          CountryDetails ON ContactMaster.CountryId = CountryDetails.CountryId AND CountryDetails.LanguageCode = @LanguageCode INNER JOIN
            //                                                          ItemDetails AS EntityItems ON ContactMaster.EntityId = EntityItems.ItemId AND EntityItems.LanguageCode = @LanguageCode INNER JOIN
            //                                                          ItemDetails AS QualificationLevelItems ON ContactMaster.QualificationLevelId = QualificationLevelItems.ItemId AND 
            //                                                          QualificationLevelItems.LanguageCode = @LanguageCode INNER JOIN
            //                                                          ItemDetails AS QualificationFieldItems ON ContactMaster.QualificationFieldId = QualificationFieldItems.ItemId AND 
            //                                                          QualificationFieldItems.LanguageCode = @LanguageCode INNER JOIN
            //                                                          ItemDetails AS QualificationSubFieldItems ON ContactMaster.QualificationSubFieldId = QualificationSubFieldItems.ItemId AND 
            //                                                          QualificationSubFieldItems.LanguageCode = @LanguageCode LEFT OUTER JOIN
            //                                                          ItemDetails AS FieldItems ON ContactMaster.FieldId = FieldItems.ItemId AND FieldItems.LanguageCode = @LanguageCode
            //                                    WHERE   ContactDetails.LanguageCode = @LanguageCode
            //                                        AND ContactMaster.EntityId = @EntityId
            //                                        AND ContactMaster.CountryId = ISNull(@CountryId,ContactMaster.CountryId)
            //                                    ORDER BY ContactDetails.Name";
            //            _Command.CommandType = CommandType.Text;

            #endregion

            #region Calling Stroed Procedure
            _Command.CommandText = "SP_GetAllMasterContacts";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            #region Passing Parameters

            _Command.Parameters.Clear();

            SqlParameter LanguageCodeParam = new SqlParameter();
            LanguageCodeParam.ParameterName = "@LanguageCode";
            LanguageCodeParam.SqlDbType = SqlDbType.Int;
            LanguageCodeParam.Value = languageCode;

            _Command.Parameters.Add(LanguageCodeParam);

            SqlParameter EntityIdParam = new SqlParameter();
            EntityIdParam.ParameterName = "@EntityId";
            EntityIdParam.SqlDbType = SqlDbType.Int;
            EntityIdParam.Value = entityId;

            _Command.Parameters.Add(EntityIdParam);

            SqlParameter CountryIdParam = new SqlParameter();
            CountryIdParam.ParameterName = "@CountryId";
            CountryIdParam.SqlDbType = SqlDbType.Int;
            if (countryId == 0)
                CountryIdParam.Value = DBNull.Value;
            else
                CountryIdParam.Value = countryId;

            _Command.Parameters.Add(CountryIdParam);

            #endregion

            _Adapter.SelectCommand = _Command;
            try
            {
                _DataSet = new DataSet();
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Adapter.Fill(_DataSet);

                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null)
                return _DataSet.Tables[0];
            else
                return null;

        }

        public static DataTable GetOneContactById(int contactId, int languageCode)
        {
            #region Setting Command Text

            //            _Command.CommandText = @"SELECT     ContactMaster.ContactId, ContactDetails.Name AS ContactName, ContactMaster.CountryId, CountryDetails.CountryName, ContactMaster.EntityId, 
            //                                                          EntityItems.Name AS EntityName, ContactMaster.IsRE, ContactMaster.IsEE, ContactMaster.ParentContactId, ContactMaster.QualificationLevelId, 
            //                                                          QualificationLevelItems.Name AS QualificationLevelName, ContactMaster.QualificationFieldId, QualificationFieldItems.Name AS QualificationFieldName, 
            //                                                          ContactMaster.QualificationSubFieldId, QualificationSubFieldItems.Name AS QualificationSubFieldName, ContactMaster.FieldId, FieldItems.Name AS FieldName, 
            //                                                          ContactDetails.LanguageCode, ContactDetails.PhoneNumber, ContactDetails.MobileNumber, ContactDetails.FaxNumber, ContactDetails.Email, 
            //                                                          ContactDetails.Website, ContactDetails.Description, ContactDetails.Experience, ContactDetails.NumberOfExperts, ContactDetails.Address, 
            //                                                          ContactDetails.JobDescription
            //                                     FROM         ContactMaster INNER JOIN
            //                                                          ContactDetails ON ContactMaster.ContactId = ContactDetails.ContactId AND ContactDetails.LanguageCode = @LanguageCode INNER JOIN
            //                                                          CountryDetails ON ContactMaster.CountryId = CountryDetails.CountryId AND CountryDetails.LanguageCode = @LanguageCode INNER JOIN
            //                                                          ItemDetails AS EntityItems ON ContactMaster.EntityId = EntityItems.ItemId AND EntityItems.LanguageCode = @LanguageCode INNER JOIN
            //                                                          ItemDetails AS QualificationLevelItems ON ContactMaster.QualificationLevelId = QualificationLevelItems.ItemId AND 
            //                                                          QualificationLevelItems.LanguageCode = @LanguageCode INNER JOIN
            //                                                          ItemDetails AS QualificationFieldItems ON ContactMaster.QualificationFieldId = QualificationFieldItems.ItemId AND 
            //                                                          QualificationFieldItems.LanguageCode = @LanguageCode INNER JOIN
            //                                                          ItemDetails AS QualificationSubFieldItems ON ContactMaster.QualificationSubFieldId = QualificationSubFieldItems.ItemId AND 
            //                                                          QualificationSubFieldItems.LanguageCode = @LanguageCode LEFT OUTER JOIN
            //                                                          ItemDetails AS FieldItems ON ContactMaster.FieldId = FieldItems.ItemId AND FieldItems.LanguageCode = @LanguageCode
            //                                     WHERE    ContactDetails.LanguageCode = @LanguageCode
            //                                         AND ContactMaster.ContactId = @ContactId";
            //            _Command.CommandType = CommandType.Text;
            #endregion

            #region Stored Procedure
            _Command.CommandText = "SP_GetOneContactById";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            #region Passing Parameters

            _Command.Parameters.Clear();

            SqlParameter LanguageCodeParam = new SqlParameter();
            LanguageCodeParam.ParameterName = "@LanguageCode";
            LanguageCodeParam.SqlDbType = SqlDbType.Int;
            LanguageCodeParam.Value = languageCode;

            _Command.Parameters.Add(LanguageCodeParam);

            SqlParameter ContactIdParam = new SqlParameter();
            ContactIdParam.ParameterName = "@ContactId";
            ContactIdParam.SqlDbType = SqlDbType.Int;
            ContactIdParam.Value = contactId;

            _Command.Parameters.Add(ContactIdParam);

            #endregion

            _Adapter.SelectCommand = _Command;
            try
            {
                _DataSet = new DataSet();

                _Adapter.Fill(_DataSet);

            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && contactId != 0)
            {
                if (_DataSet.Tables[0].Rows.Count == 0)
                {
                    DataRow dr = _DataSet.Tables[0].NewRow();

                    dr["ContactId"] = contactId;
                    dr["CountryId"] = 0;
                    dr["EntityId"] = 0;
                    dr["IsRE"] = false;
                    dr["IsEE"] = false;
                    dr["ParentContactId"] = 0;
                    dr["QualificationLevelId"] = 0;
                    dr["QualificationFieldId"] = 0;
                    dr["QualificationSubFieldId"] = 0;
                    dr["FieldId"] = 0;
                    dr["ParentContactCountryId"] = 0;
                    dr["ParentContactEntityId"] = 0;

                    _DataSet.Tables[0].Rows.Add(dr);

                    return _DataSet.Tables[0];
                }
                else
                    return _DataSet.Tables[0];
            }
            else
                return null;
        }

        public static DataTable GetRelatedContacts(int contactId, int languageCode)
        {

            #region Calling Stroed Procedure
            _Command.CommandText = "SP_GetRelatedContacts";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            #region Passing Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = languageCode;
            _Command.Parameters.Add("@ContactId", SqlDbType.Int).Value = contactId;

            #endregion

            _Adapter.SelectCommand = _Command;
            try
            {
                _DataSet = new DataSet();
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Adapter.Fill(_DataSet);

                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null)
                return _DataSet.Tables[0];
            else
                return null;

        }

        public static DataTable GetRelatedProjects(int contactId, int languageCode)
        {
            #region Calling Stroed Procedure
            _Command.CommandText = "SP_GetRelatedProjects";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            #region Passing Parameters

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = languageCode;
            _Command.Parameters.Add("@ContactId", SqlDbType.Int).Value = contactId;

            #endregion

            _Adapter.SelectCommand = _Command;
            try
            {
                _DataSet = new DataSet();
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Adapter.Fill(_DataSet);

                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null)
                return _DataSet.Tables[0];
            else
                return null;

        }
        #endregion

        #region Update

        public static bool UpdateContact(Contact _contact)
        {
            #region Setting Command Text
            //            _Command.CommandText = @"UPDATE    ContactMaster 
            //                                      SET           CountryId =@CountryId,
            //                                                    IsRE =@IsRE,
            //                                                    IsEE =@IsEE,
            //                                                    QualificationLevelId =@QualificationLevelId,
            //                                                    QualificationFieldId =@QualificationFieldId,
            //                                                    QualificationSubFieldId =@QualificationSubFieldId,
            //                                                    FieldId =@FieldId
            //                                        WHERE  ContactId=@ContactId  ;
            //            
            //                                       UPDATE ContactDetails
            //                                       SET    Name = @ContactName,
            //                                              PhoneNumber = @PhoneNumber,
            //                                              MobileNumber = @MobileNumber,
            //                                              FaxNumber = @FaxNumber,
            //                                              Email = @Email,
            //                                              Website = @Website,
            //                                              Description = @Description,
            //                                              Experience = @Experience, 
            //                                              NumberOfExperts = @NumberOfExperts,
            //                                              Address = @Address,
            //                                              JobDescription = @JobDescription
            //                                       WHERE  ContactId = @ContactId
            //                                              AND LanguageCode = @LanguageCode";
            //            _Command.CommandType = CommandType.Text;
            #endregion

            #region Strored Procedure
            _Command.CommandText = "SP_UpdateContact";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            #region Passing Parameters
            _Command.Parameters.Clear();

            // Contact Id
            SqlParameter ContactIdParam = new SqlParameter();
            ContactIdParam.ParameterName = "@ContactId";
            ContactIdParam.SqlDbType = SqlDbType.Int;
            ContactIdParam.Value = _contact.ContactId;
            _Command.Parameters.Add(ContactIdParam);

            // Is RE
            SqlParameter IsREParam = new SqlParameter();
            IsREParam.ParameterName = "@IsRE";
            IsREParam.SqlDbType = SqlDbType.Bit;
            IsREParam.Value = _contact.IsRE;
            _Command.Parameters.Add(IsREParam);

            // Is EE
            SqlParameter IsEEParam = new SqlParameter();
            IsEEParam.ParameterName = "@IsEE";
            IsEEParam.SqlDbType = SqlDbType.Bit;
            IsEEParam.Value = _contact.IsEE;
            _Command.Parameters.Add(IsEEParam);

            // Language Code
            SqlParameter LanguageCodeParam = new SqlParameter();
            LanguageCodeParam.ParameterName = "@LanguageCode";
            LanguageCodeParam.SqlDbType = SqlDbType.Int;
            LanguageCodeParam.Value = _contact.LanguageCode;
            _Command.Parameters.Add(LanguageCodeParam);

            // ParentContactId
            SqlParameter ParentContactIdParam = new SqlParameter();
            ParentContactIdParam.ParameterName = "@ParentContactId";
            ParentContactIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.ParentContactId == 0)
                ParentContactIdParam.Value = DBNull.Value;
            else
                ParentContactIdParam.Value = _contact.ParentContactId;
            _Command.Parameters.Add(ParentContactIdParam);


            // Country Id
            SqlParameter CountryIdParam = new SqlParameter();
            CountryIdParam.ParameterName = "@CountryId";
            CountryIdParam.SqlDbType = SqlDbType.Int;
            CountryIdParam.Value = _contact.CountryId;
            _Command.Parameters.Add(CountryIdParam);

            // EntityId Id
            SqlParameter EntityIdParam = new SqlParameter();
            EntityIdParam.ParameterName = "@EntityId";
            EntityIdParam.SqlDbType = SqlDbType.Int;
            EntityIdParam.Value = _contact.EntityId;
            _Command.Parameters.Add(EntityIdParam);

            // Qualification Level Id
            SqlParameter QualificationLevelIdParam = new SqlParameter();
            QualificationLevelIdParam.ParameterName = "@QualificationLevelId";
            QualificationLevelIdParam.SqlDbType = SqlDbType.Int;

            if (_contact.QualificationLevelId != 0)
                QualificationLevelIdParam.Value = _contact.QualificationLevelId;
            else
                QualificationLevelIdParam.Value = DBNull.Value;

            _Command.Parameters.Add(QualificationLevelIdParam);

            // Qualification Field Id
            SqlParameter QualificationFieldIdParam = new SqlParameter();
            QualificationFieldIdParam.ParameterName = "@QualificationFieldId";
            QualificationFieldIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.QualificationFieldId == 0)
                QualificationFieldIdParam.Value = DBNull.Value;
            else
                QualificationFieldIdParam.Value = _contact.QualificationFieldId;
            _Command.Parameters.Add(QualificationFieldIdParam);

            // Qualification SubField Id
            SqlParameter QualificationSubFieldIdParam = new SqlParameter();
            QualificationSubFieldIdParam.ParameterName = "@QualificationSubFieldId";
            QualificationSubFieldIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.QualificationSubFieldId == 0)
                QualificationSubFieldIdParam.Value = DBNull.Value;
            else
                QualificationSubFieldIdParam.Value = _contact.QualificationSubFieldId;
            _Command.Parameters.Add(QualificationSubFieldIdParam);

            // Field Id
            SqlParameter FieldIdParam = new SqlParameter();
            FieldIdParam.ParameterName = "@FieldId";
            FieldIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.FieldId == 0)
                FieldIdParam.Value = DBNull.Value;
            else
                FieldIdParam.Value = _contact.FieldId;
            _Command.Parameters.Add(FieldIdParam);

            // Contact Name
            SqlParameter NameParam = new SqlParameter();
            NameParam.ParameterName = "@ContactName";
            NameParam.SqlDbType = SqlDbType.NVarChar;
            NameParam.Value = _contact.ContactName;
            _Command.Parameters.Add(NameParam);

            // Phone Number
            SqlParameter PhoneNumberParam = new SqlParameter();
            PhoneNumberParam.ParameterName = "@PhoneNumber";
            PhoneNumberParam.SqlDbType = SqlDbType.NVarChar;
            PhoneNumberParam.Value = _contact.PhoneNumber;
            _Command.Parameters.Add(PhoneNumberParam);

            // Mobile Number
            SqlParameter MobileNumberParam = new SqlParameter();
            MobileNumberParam.ParameterName = "@MobileNumber";
            MobileNumberParam.SqlDbType = SqlDbType.NVarChar;
            MobileNumberParam.Value = _contact.MobileNumber;
            _Command.Parameters.Add(MobileNumberParam);

            // Fax Number
            SqlParameter FaxNumberParam = new SqlParameter();
            FaxNumberParam.ParameterName = "@FaxNumber";
            FaxNumberParam.SqlDbType = SqlDbType.NVarChar;
            FaxNumberParam.Value = _contact.FaxNumber;
            _Command.Parameters.Add(FaxNumberParam);

            // Email
            SqlParameter EmailParam = new SqlParameter();
            EmailParam.ParameterName = "@Email";
            EmailParam.SqlDbType = SqlDbType.NVarChar;
            EmailParam.Value = _contact.Email;
            _Command.Parameters.Add(EmailParam);

            // Website
            SqlParameter WebsiteParam = new SqlParameter();
            WebsiteParam.ParameterName = "@Website";
            WebsiteParam.SqlDbType = SqlDbType.NVarChar;
            WebsiteParam.Value = _contact.Website;
            _Command.Parameters.Add(WebsiteParam);

            // Description
            SqlParameter DescriptopnParam = new SqlParameter();
            DescriptopnParam.ParameterName = "@Description";
            DescriptopnParam.SqlDbType = SqlDbType.Text;
            DescriptopnParam.Value = _contact.Description;
            _Command.Parameters.Add(DescriptopnParam);

            // Experience
            SqlParameter ExperienceParam = new SqlParameter();
            ExperienceParam.ParameterName = "@Experience";
            ExperienceParam.SqlDbType = SqlDbType.Int;
            if (_contact.Experience == -1)
                ExperienceParam.Value = DBNull.Value;
            else
                ExperienceParam.Value = _contact.Experience;

            _Command.Parameters.Add(ExperienceParam);

            // Number Of Experts
            SqlParameter NumberOfExpertsParam = new SqlParameter();
            NumberOfExpertsParam.ParameterName = "@NumberOfExperts";
            NumberOfExpertsParam.SqlDbType = SqlDbType.Int;
            if (_contact.NumberOfExperts == -1)
                NumberOfExpertsParam.Value = DBNull.Value;
            else
                NumberOfExpertsParam.Value = _contact.NumberOfExperts;
            _Command.Parameters.Add(NumberOfExpertsParam);

            // Address
            SqlParameter AddressParam = new SqlParameter();
            AddressParam.ParameterName = "@Address";
            AddressParam.SqlDbType = SqlDbType.NVarChar;
            AddressParam.Value = _contact.Address;
            _Command.Parameters.Add(AddressParam);

            // Job Description
            SqlParameter JobDescriptionParam = new SqlParameter();
            JobDescriptionParam.ParameterName = "@JobDescription";
            JobDescriptionParam.SqlDbType = SqlDbType.NVarChar;
            JobDescriptionParam.Value = _contact.JobDescription;
            _Command.Parameters.Add(JobDescriptionParam);

            #endregion

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return true;

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return false;
            }

        }

        #endregion

        #region Insert

        public static bool InsertContact(Contact _contact)
        {
            #region Setting Command Text
            //            _Command.CommandText = @"INSERT INTO ContactMaster
            //                                    (CountryId, EntityId, IsRE, IsEE, QualificationLevelId, QualificationFieldId, QualificationSubFieldId, FieldId)
            //                                    VALUES     (@CountryId,@EntityId,@IsRE,@IsEE,@QualificationLevelId,@QualificationFieldId,@QualificationSubFieldId,@FieldId);
            //
            //                                    DECLARE @ContactId int;
            //                                    SELECT @ContactId  = scope_identity();
            //
            //                                    INSERT INTO ContactDetails
            //                                            (ContactId, LanguageCode, Name, PhoneNumber, MobileNumber, FaxNumber, Email, Website, Description,
            //                                             Experience, NumberOfExperts, Address, JobDescription)
            //                                      VALUES     (@ContactId,@LanguageCode,@ContactName,@PhoneNumber,@MobileNumber,@FaxNumber,
            //                                                @Email,@Website,@Description,@Experience,@NumberOfExperts,@Address,@JobDescription);";
            //            _Command.CommandType = CommandType.Text;
            #endregion

            #region Stored Procedure
            _Command.CommandText = "SP_InsertContact";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            #region Passing Parameters
            _Command.Parameters.Clear();

            // Entity Id
            SqlParameter EntityIdParam = new SqlParameter();
            EntityIdParam.ParameterName = "@EntityId";
            EntityIdParam.SqlDbType = SqlDbType.Int;
            EntityIdParam.Value = _contact.EntityId;
            _Command.Parameters.Add(EntityIdParam);

            // Is RE
            SqlParameter IsREParam = new SqlParameter();
            IsREParam.ParameterName = "@IsRE";
            IsREParam.SqlDbType = SqlDbType.Bit;
            IsREParam.Value = _contact.IsRE;
            _Command.Parameters.Add(IsREParam);

            // Is EE
            SqlParameter IsEEParam = new SqlParameter();
            IsEEParam.ParameterName = "@IsEE";
            IsEEParam.SqlDbType = SqlDbType.Bit;
            IsEEParam.Value = _contact.IsEE;
            _Command.Parameters.Add(IsEEParam);

            // Language Code
            SqlParameter LanguageCodeParam = new SqlParameter();
            LanguageCodeParam.ParameterName = "@LanguageCode";
            LanguageCodeParam.SqlDbType = SqlDbType.Int;
            LanguageCodeParam.Value = _contact.LanguageCode;
            _Command.Parameters.Add(LanguageCodeParam);

            // ParentContactId
            SqlParameter ParentContactIdParam = new SqlParameter();
            ParentContactIdParam.ParameterName = "@ParentContactId";
            ParentContactIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.ParentContactId == 0)
                ParentContactIdParam.Value = DBNull.Value;
            else
                ParentContactIdParam.Value = _contact.ParentContactId;
            _Command.Parameters.Add(ParentContactIdParam);


            // Country Id
            SqlParameter CountryIdParam = new SqlParameter();
            CountryIdParam.ParameterName = "@CountryId";
            CountryIdParam.SqlDbType = SqlDbType.Int;
            CountryIdParam.Value = _contact.CountryId;
            _Command.Parameters.Add(CountryIdParam);

            // Qualification Level Id
            SqlParameter QualificationLevelIdParam = new SqlParameter();
            QualificationLevelIdParam.ParameterName = "@QualificationLevelId";
            QualificationLevelIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.QualificationLevelId == 0)
                QualificationLevelIdParam.Value = DBNull.Value;
            else
                QualificationLevelIdParam.Value = _contact.QualificationLevelId;
            _Command.Parameters.Add(QualificationLevelIdParam);

            // Qualification Field Id
            SqlParameter QualificationFieldIdParam = new SqlParameter();
            QualificationFieldIdParam.ParameterName = "@QualificationFieldId";
            QualificationFieldIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.QualificationFieldId == 0)
                QualificationFieldIdParam.Value = DBNull.Value;
            else
                QualificationFieldIdParam.Value = _contact.QualificationFieldId;
            _Command.Parameters.Add(QualificationFieldIdParam);

            // Qualification SubField Id
            SqlParameter QualificationSubFieldIdParam = new SqlParameter();
            QualificationSubFieldIdParam.ParameterName = "@QualificationSubFieldId";
            QualificationSubFieldIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.QualificationSubFieldId == 0)
                QualificationSubFieldIdParam.Value = DBNull.Value;
            else
                QualificationSubFieldIdParam.Value = _contact.QualificationSubFieldId;
            _Command.Parameters.Add(QualificationSubFieldIdParam);

            // Field Id
            SqlParameter FieldIdParam = new SqlParameter();
            FieldIdParam.ParameterName = "@FieldId";
            FieldIdParam.SqlDbType = SqlDbType.Int;
            if (_contact.FieldId == 0)
                FieldIdParam.Value = DBNull.Value;
            else
                FieldIdParam.Value = _contact.FieldId;
            _Command.Parameters.Add(FieldIdParam);


            // Contact Name
            SqlParameter NameParam = new SqlParameter();
            NameParam.ParameterName = "@ContactName";
            NameParam.SqlDbType = SqlDbType.NVarChar;
            NameParam.Value = _contact.ContactName;
            _Command.Parameters.Add(NameParam);

            // Phone Number
            SqlParameter PhoneNumberParam = new SqlParameter();
            PhoneNumberParam.ParameterName = "@PhoneNumber";
            PhoneNumberParam.SqlDbType = SqlDbType.NVarChar;
            PhoneNumberParam.Value = _contact.PhoneNumber;
            _Command.Parameters.Add(PhoneNumberParam);

            // Mobile Number
            SqlParameter MobileNumberParam = new SqlParameter();
            MobileNumberParam.ParameterName = "@MobileNumber";
            MobileNumberParam.SqlDbType = SqlDbType.NVarChar;
            MobileNumberParam.Value = _contact.MobileNumber;
            _Command.Parameters.Add(MobileNumberParam);

            // Fax Number
            SqlParameter FaxNumberParam = new SqlParameter();
            FaxNumberParam.ParameterName = "@FaxNumber";
            FaxNumberParam.SqlDbType = SqlDbType.NVarChar;
            FaxNumberParam.Value = _contact.FaxNumber;
            _Command.Parameters.Add(FaxNumberParam);

            // Email
            SqlParameter EmailParam = new SqlParameter();
            EmailParam.ParameterName = "@Email";
            EmailParam.SqlDbType = SqlDbType.NVarChar;
            EmailParam.Value = _contact.Email;
            _Command.Parameters.Add(EmailParam);

            // Website
            SqlParameter WebsiteParam = new SqlParameter();
            WebsiteParam.ParameterName = "@Website";
            WebsiteParam.SqlDbType = SqlDbType.NVarChar;
            WebsiteParam.Value = _contact.Website;
            _Command.Parameters.Add(WebsiteParam);

            // Description
            SqlParameter DescriptopnParam = new SqlParameter();
            DescriptopnParam.ParameterName = "@Description";
            DescriptopnParam.SqlDbType = SqlDbType.Text;
            DescriptopnParam.Value = _contact.Description;
            _Command.Parameters.Add(DescriptopnParam);

            // Experience
            SqlParameter ExperienceParam = new SqlParameter();
            ExperienceParam.ParameterName = "@Experience";
            ExperienceParam.SqlDbType = SqlDbType.Int;
            if (_contact.Experience == -1)
                ExperienceParam.Value = DBNull.Value;
            else
                ExperienceParam.Value = _contact.Experience;

            _Command.Parameters.Add(ExperienceParam);

            // Number Of Experts
            SqlParameter NumberOfExpertsParam = new SqlParameter();
            NumberOfExpertsParam.ParameterName = "@NumberOfExperts";
            NumberOfExpertsParam.SqlDbType = SqlDbType.Int;
            if (_contact.NumberOfExperts == -1)
                NumberOfExpertsParam.Value = DBNull.Value;
            else
                NumberOfExpertsParam.Value = _contact.NumberOfExperts;
            _Command.Parameters.Add(NumberOfExpertsParam);

            // Address
            SqlParameter AddressParam = new SqlParameter();
            AddressParam.ParameterName = "@Address";
            AddressParam.SqlDbType = SqlDbType.NVarChar;
            AddressParam.Value = _contact.Address;
            _Command.Parameters.Add(AddressParam);

            // Job Description
            SqlParameter JobDescriptionParam = new SqlParameter();
            JobDescriptionParam.ParameterName = "@JobDescription";
            JobDescriptionParam.SqlDbType = SqlDbType.NVarChar;
            JobDescriptionParam.Value = _contact.JobDescription;
            _Command.Parameters.Add(JobDescriptionParam);

            #endregion

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return true;

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return false;
            }
        }

        #endregion


        #region Delete
        public static bool DeleteContact(Contact _contact)
        {
            #region Command Text
            //            _Command.CommandText = @" DELETE FROM ContactDetails
            //                                      WHERE ContactId = @ContactId 
            //                                       AND LanguageCode = @LanguageCode";
            //            _Command.CommandType = CommandType.Text;
            #endregion

            #region stored procedure
            _Command.CommandText = "SP_DeleteOneCotact";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            _Command.Parameters.Clear();

            SqlParameter ContactIdParam = new SqlParameter();
            ContactIdParam.ParameterName = "@ContactId";
            ContactIdParam.SqlDbType = SqlDbType.Int;
            ContactIdParam.Value = _contact.ContactId;

            _Command.Parameters.Add(ContactIdParam);

            SqlParameter LanguageCodeParam = new SqlParameter();
            LanguageCodeParam.ParameterName = "@LanguageCode";
            LanguageCodeParam.SqlDbType = SqlDbType.Int;
            LanguageCodeParam.Value = _contact.LanguageCode;

            _Command.Parameters.Add(LanguageCodeParam);

            try
            {
                // Open Connection
                if (_Connection.State != ConnectionState.Open)
                    _Connection.Open();

                _Command.ExecuteNonQuery();

                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return true;

            }
            catch (Exception exp)
            {
                // Close Connection
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();

                return false;
            }
        }
        #endregion

        #endregion

        #region General Methods

        public static DataTable GetAllCountries(int LanguageCode, string FirstItem)
        {
            #region command text
            //            _Command.CommandText = @"SELECT  CountryId, CountryName
            //                                     FROM    CountryDetails
            //                                     WHERE   LanguageCode = @LanguageCode";

            //            _Command.CommandType = CommandType.Text;
            #endregion

            #region strored procedure
            _Command.CommandText = "SP_GetAllCountries";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            _DataSet = new DataSet();

            _Command.Parameters.Clear();

            SqlParameter LanguageCodeParam = new SqlParameter();
            LanguageCodeParam.ParameterName = "@LanguageCode";
            LanguageCodeParam.SqlDbType = SqlDbType.Int;
            LanguageCodeParam.Value = LanguageCode;

            _Command.Parameters.Add(LanguageCodeParam);

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                DataRow dr = _DataSet.Tables[0].NewRow();
                dr["CountryId"] = 0;

                if (LanguageCode == 1033)
                    dr["CountryName"] = "Please select a county from the list";
                else if (LanguageCode == 1036)
                    dr["CountryName"] = "S'il vous plait sélectionner un pays de la liste";
                else if (LanguageCode == 1025)
                    dr["CountryName"] = "من فضلك إختر دولة من القائمة";

                _DataSet.Tables[0].Rows.InsertAt(dr, 0);
                return _DataSet.Tables[0];
            }
            else
                return null;

        }

        public static DataTable GetContactCountries(int LanguageCode)
        {
            #region Command Text
            //            _Command.CommandText = @"SELECT DISTINCT ContactMaster.CountryId, CountryDetails.CountryName
            //                                     FROM         ContactMaster INNER JOIN
            //                                                          CountryDetails ON ContactMaster.CountryId = CountryDetails.CountryId
            //                                     WHERE dbo.CountryDetails.LanguageCode = @LanguageCode ";
            //            _Command.CommandType = CommandType.Text;
            #endregion
            #region Stored procedure
            _Command.CommandText = "SP_GetAllCountries";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            _DataSet = new DataSet();

            _Command.Parameters.Clear();

            SqlParameter LanguageCodeParam = new SqlParameter();
            LanguageCodeParam.ParameterName = "@LanguageCode";
            LanguageCodeParam.SqlDbType = SqlDbType.Int;
            LanguageCodeParam.Value = LanguageCode;

            _Command.Parameters.Add(LanguageCodeParam);

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return _DataSet.Tables[0];
            else
                return null;

        }

        public static DataTable GetAllItems(int LanguageCode)
        {
            #region Command Text

            //            _Command.CommandText = @" SELECT  ItemId,
            //                                            Name AS ItemName
            //                                      FROM    dbo.ItemDetails
            //                                      WHERE LanguageCode = @LanguageCode";
            //            _Command.CommandType = CommandType.Text;

            #endregion

            #region Stored procedure
            _Command.CommandText = "SP_GetAllItems";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            _DataSet = new DataSet();

            _Command.Parameters.Clear();

            SqlParameter LanguageCodeParam = new SqlParameter();
            LanguageCodeParam.ParameterName = "@LanguageCode";
            LanguageCodeParam.SqlDbType = SqlDbType.Int;
            LanguageCodeParam.Value = LanguageCode;

            _Command.Parameters.Add(LanguageCodeParam);

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return _DataSet.Tables[0];
            else
                return null;

        }

        public static List<TinyItem> GetAvailableParentContacts(int CurrentContactId, int CountryId, int EntityId, int LanguageCode)
        {
            _Command.CommandText = "SP_GetAvailableParentContacts";
            _Command.CommandType = CommandType.StoredProcedure;

            _DataSet = new DataSet();

            _Command.Parameters.Clear();

            // Current ContactId Parameter
            _Command.Parameters.Add("@CurrenContactId", SqlDbType.Int).Value = CurrentContactId;

            // CountryId Parameter
            if (CountryId != 0)
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = CountryId;
            else
                _Command.Parameters.Add("@CountryId", SqlDbType.Int).Value = DBNull.Value;


            // EntityId Parameter
            if (EntityId != 0)
                _Command.Parameters.Add("@EntityId", SqlDbType.Int).Value = EntityId;
            else
                _Command.Parameters.Add("@EntityId", SqlDbType.Int).Value = DBNull.Value;

            // Language Code Parameter
            _Command.Parameters.Add("@LanguageCode", SqlDbType.Int).Value = LanguageCode;

            _Adapter.SelectCommand = _Command;

            try
            {

                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
            {
                List<TinyItem> lstAvailableParentConacts = new List<TinyItem>();

                foreach (DataRow dr in _DataSet.Tables[0].Rows)
                {
                    TinyItem tempItem = new TinyItem();
                    tempItem.ID = Convert.ToInt32(dr["ParentContactId"]);
                    tempItem.Name = Convert.ToString(dr["ParentContactName"]);

                    lstAvailableParentConacts.Add(tempItem);
                }
                TinyItem emptyItem = new TinyItem();
                emptyItem.ID = 0;
                emptyItem.Name = "";
                lstAvailableParentConacts.Insert(0, emptyItem);
                return lstAvailableParentConacts;

            }
            else
                return null;


        }

        public static int GetContactCountryId(int ContactId)
        {
            #region strored procedure
            _Command.CommandText = "SP_GetContacCountryId";
            _Command.CommandType = CommandType.StoredProcedure;
            #endregion

            _DataSet = new DataSet();

            _Command.Parameters.Clear();

            _Command.Parameters.Add("@ContactId", SqlDbType.Int).Value = ContactId;

            _Adapter.SelectCommand = _Command;

            try
            {
                _Adapter.Fill(_DataSet);
            }
            catch (Exception exp)
            {
                if (_Connection.State != ConnectionState.Closed)
                    _Connection.Close();
            }

            if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
                return Convert.ToInt32(_DataSet.Tables[0].Rows[0][0]);
            else
                return 0;
        }

        #endregion
    }
}
